'use strict';

const {Service} = require('ee-core');
const MysqlDB = require("../utils/mysql");
const {getDataBaseInfo, insertJsonDbRow, updateJsonDbRow, deleteJsonDbRow} = require('../utils/StorageJson')

class MysqlMngService extends Service {

    constructor(ctx) {
        super(ctx);
    }

    async showDataBase(connect) {
        return new MysqlDB().executeQuery(connect, 'SELECT *  FROM information_schema.schemata;');
    }

    async showDatabaseTables(connect) {
        let localData = JSON.parse(connect);
        let res = {code: 200, data: {}, message: ''}
        let tableWhere=`where TABLE_SCHEMA='${localData.params.databaseKey}'`
        if(localData.params.databaseKey!=='information_schema'&&localData.params.databaseKey!=='performance_schema'){
            tableWhere+=" AND TABLE_TYPE='BASE TABLE'"
        }
        tableWhere+=";"

        let tableRes = await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.TABLES ${tableWhere}`);
        if (tableRes.code === 200) {
            res.data.tableList = []
            tableRes.data?.dataList.forEach(item => {
                let tableRow = {}
                Object.keys(item).forEach(key => {
                    tableRow[key.toLowerCase()] = item[key]
                })
                res.data.tableList.push(tableRow)
            });
        }else {
            return tableRes
        }
        let viewsRes = await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.VIEWS where TABLE_SCHEMA='${localData.params.databaseKey}';`);
        if (viewsRes.code === 200) {
            res.data.viewList = []
            viewsRes.data?.dataList.forEach(item => {
                let viewRow = {}
                Object.keys(item).forEach(key => {
                    viewRow[key.toLowerCase()] = item[key]
                })
                res.data.viewList.push(viewRow)
            });
        }
        let functionRes = await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.ROUTINES where ROUTINE_SCHEMA='${localData.params.databaseKey}';`);
        if (functionRes.code == 200) {
            res.data.functionList = []
            functionRes.data?.dataList.forEach(item => {
                let functionRow = {}
                Object.keys(item).forEach(key => {
                    functionRow[key.toLowerCase()] = item[key]
                })
                res.data.functionList.push(functionRow)
            });
        }
        let keyWordRes = await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.KEYWORDS WHERE RESERVED=1;`);
        if (keyWordRes.code == 200) {
            res.data.keyWordList = []
            keyWordRes.data?.dataList.forEach(item => {
                let keyWordRow = {}
                Object.keys(item).forEach(key => {
                    keyWordRow[key.toLowerCase()] = item[key]
                })
                res.data.keyWordList.push(keyWordRow)
            });
        }
        return res;
    }

    async pingConnect(args) {
        return new MysqlDB().pingDatabase(args);
    }

    async charSetNameOptions(connect) {
        return await new MysqlDB().executeQuery(connect, 'SELECT * FROM information_schema.CHARACTER_SETS ORDER BY CHARACTER_SET_NAME ASC ;');
    }

    async collationNameOptions(connect) {
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where CHARACTER_SET_NAME='${localData.params.charSetName}' ORDER BY COLLATION_NAME ASC;`);
    }

    async createDatabase(connect) {
        return this.operateDatabase(connect, false)
    }

    async alterDatabase(connect) {
        return this.operateDatabase(connect, true)
    }

    async operateDatabase(connect, isEdit) {
        let localData = JSON.parse(connect);
        let script = ''
        if (isEdit) {
            script = `ALTER DATABASE \`${localData.params.databaseName}\``
        } else {
            script = `CREATE DATABASE \`${localData.params.databaseName}\``
        }
        if (localData.params.charSetName) {
            script = `${script} CHARACTER SET '${localData.params.charSetName}'`
        }
        if (localData.params.collationName) {
            script = `${script}  COLLATE '${localData.params.collationName}' `
        }
        script = `${script} ;`
        return await new MysqlDB().executeQuery(localData, script);
    }

    async selectTableList(connect) {
        let localData = JSON.parse(connect);
        let tableWhere=`where TABLE_SCHEMA='${localData.params.databaseKey}'`
        if(localData.params.databaseKey!=='information_schema'&&localData.params.databaseKey!=='performance_schema'){
            tableWhere+=" AND TABLE_TYPE='BASE TABLE'"
        }
        tableWhere+=";"
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.TABLES ${tableWhere}`);
    }

    async selectTableFields(connect) {
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.COLUMNS where TABLE_SCHEMA='${localData.params.databaseKey}' AND TABLE_NAME='${localData.params.tableKey}' ORDER BY ORDINAL_POSITION ;`);
    }

    async selectTableEngines(connect) {
        return await new MysqlDB().executeQuery(connect, `SELECT * FROM information_schema.ENGINES ORDER BY SUPPORT;`);
    }

    getCondition(sqlWhere, sqlOrder) {
        let whereSql = null;
        if ((!sqlWhere && !sqlOrder) || (sqlWhere.length < 1 && sqlOrder.length < 1)) {
            return whereSql;
        }
        whereSql = ''
        sqlWhere.forEach(row => {
            whereSql = whereSql + "`" + row.field + "`"
            if (row.symbol === 'likeAll') {
                whereSql = whereSql + " LIKE '%" + row.fieldValue + "%'"
            } else if (row.symbol === 'notLikeAll') {
                whereSql = whereSql + " NOT LIKE '%" + row.fieldValue + "%'"
            } else if (row.symbol === 'likeLeft') {
                whereSql = whereSql + " LIKE '%" + row.fieldValue + "'"
            } else if (row.symbol === 'likeRight') {
                whereSql = whereSql + " LIKE '" + row.fieldValue + "%'"
            } else if (row.symbol === 'notLikeLeft') {
                whereSql = whereSql + " NOT LIKE '%" + row.fieldValue + "'"
            } else if (row.symbol === 'notLikeRight') {
                whereSql = whereSql + " NOT LIKE '" + row.fieldValue + "%'"
            } else if (row.symbol === 'isNull') {
                whereSql = whereSql + ' IS NULL'
            } else if (row.symbol === 'isNotNull') {
                whereSql = whereSql + ' IS NOT NULL'
            } else if (row.symbol === 'isEmpty') {
                whereSql = whereSql + "=''"
            } else if (row.symbol === 'isNotEmpty') {
                whereSql = whereSql + "!=''"
            } else if (row.symbol === 'between') {
                whereSql = whereSql + " BETWEEN '" + row.fieldValue + "' AND '" + row.secondFieldValue + "'"
            } else if (row.symbol === 'notBetween') {
                whereSql = whereSql + " NOT BETWEEN '" + row.fieldValue + "' AND '" + row.secondFieldValue + "'"
            } else if (row.symbol === 'in') {
                whereSql = whereSql + " IN (" + row.fieldValue + ")"
            } else if (row.symbol === 'notIn') {
                whereSql = whereSql + " NOT IN (" + row.fieldValue + ")"
            } else {
                whereSql = whereSql + row.symbol + "'" + row.fieldValue + "'"
            }
            if (row.constructorType) {
                whereSql = `${whereSql}  ${row.constructorType}`
            }
        })
        if(sqlWhere&&sqlWhere.length>0){
            whereSql = `WHERE  ${whereSql}   `
        }
        let orderArray = []
        sqlOrder.forEach(row => {
            let orderItem = " `" + row.orderField + "`"
            if (row.orderType === 'DESC') {
                orderItem = `${orderItem} DESC`
            }
            orderArray.push(orderItem)
        })
        if (orderArray.length > 0) {
            whereSql = `${whereSql}  ORDER BY  ${orderArray.join(',')}`
        }
        return whereSql;
    }

    async selectTableDataList(connect) {
        let localData = JSON.parse(connect);
        let condition = this.getCondition(localData.params.sqlWhere, localData.params.sqlOrder)
        let script = `SELECT * FROM ${localData.params.tableKey} `;
        if (condition) {
            script = `${script} ${condition}`
        }
        script = `${script} ;`
        return await new MysqlDB().executeQuery(connect, script);
    }

    async selectTableDataPageList(connect) {
        let localData = JSON.parse(connect);
        let condition = this.getCondition(localData.params.sqlWhere, localData.params.sqlOrder)
        let countScript = `SELECT COUNT(1) as total FROM ${localData.params.tableKey} `
        if (condition) {
            countScript = `${countScript} ${condition}`
        }
        countScript = `${countScript} ;`
        let countRes = await new MysqlDB().executeQuery(connect, countScript)
        if (countRes.code != 200) {
            return countRes;
        }
        let script = `SELECT * FROM ${localData.params.tableKey}  `
        if (condition) {
            script = `${script} ${condition}`
        }
        if (localData.params.pageNumber === 1) {
            script = `${script} LIMIT ${localData.params.pageSize}`
        } else {
            script = `${script} LIMIT ${(localData.params.pageNumber - 1) * localData.params.pageSize},${localData.params.pageSize}`
        }
        script = `${script} ;`
        let queryRes = await new MysqlDB().executeQuery(connect, script);
        queryRes.total = countRes.data?.dataList[0].total
        return queryRes
    }

    async addTableRow(connect) {
        let localData = JSON.parse(connect);
        let script = 'INSERT INTO `' + localData.params.databaseName + '`.`' + localData.params.tableKey + '`'
        let keyArray = []
        let valueArray = []
        Object.keys(localData.params.tableForm).forEach(key => {
            let value = localData.params.tableForm[key]
            if (value) {
                keyArray.push(key)
                valueArray.push(value)
            }
        })
        script = script + ' (' + keyArray.join(',') + ')  VALUES (\'' + valueArray.join("','") + '\');'

        return await new MysqlDB().executeQuery(localData, script);
    }

    async editTableRow(connect) {
        let localData = JSON.parse(connect);
        let script = 'UPDATE `' + localData.params.databaseName + '`.`' + localData.params.tableKey + '`'
        let setValues = []
        let whereValues = []
        Object.keys(localData.params.selectRow).forEach(key => {
            let oldValue = localData.params.selectRow[key]
            let newValue = localData.params.tableForm[key]
            if (oldValue !== newValue) {
                setValues.push(`\`${key}\` = '${newValue}'`)
            }
            whereValues.push(`\`${key}\` = '${oldValue}'`)
        })
        if (setValues.length < 1) {
            return {code: 200, message: '成功', data: {}}
        }
        script = script + `  SET   ${setValues.join(',')} WHERE ${whereValues.join(' AND ')};`
        return await new MysqlDB().executeQuery(localData, script);
    }

    async deleteTableRow(connect) {
        let localData = JSON.parse(connect);
        let script = 'DELETE  FROM `' + localData.params.databaseName + '`.`' + localData.params.tableKey + '`'
        let whereValues = []
        Object.keys(localData.params.selectRow).forEach(key => {
            let oldValue = localData.params.selectRow[key]
            whereValues.push(`\`${key}\` = '${oldValue}'`)
        })
        script = script + `   WHERE ${whereValues.join(' AND ')};`
        return await new MysqlDB().executeQuery(localData, script);
    }

    async runConsole(connect) {
        let localData = JSON.parse(connect);
        if (!localData.params.runScript || localData.params.runScript.trim().length < 1) {
            return {code: 500, data: [], message: '执行脚本不能为空'}
        }
        return await new MysqlDB().executeQuery(localData, localData.params.runScript);
    }

    async saveQueryScript(connect) {
        let localData = JSON.parse(connect);
        let saveKey = localData.mainData.id + '_' + localData.mainData.database
        let row = {}
        row.mainData = localData.params
        let isSave
        if (row.mainData.id) {
            isSave = updateJsonDbRow(saveKey, row)
        } else {
            isSave = insertJsonDbRow(saveKey, row)
        }
        return {code: 200, data: row.mainData.id, message: isSave ? '操作成功' : '操作失败'}
    }

    async getQueryScript(connect) {
        let localData = JSON.parse(connect);
        let saveKey = localData.mainData.id + '_' + localData.mainData.database
        let res = {code: 200, data: getDataBaseInfo(saveKey), message: '操作成功'}
        return res
    }

    async deleteQueryScript(connect) {
        let localData = JSON.parse(connect);
        let saveKey = localData.mainData.id + '_' + localData.mainData.database
        let isDel = deleteJsonDbRow(saveKey, localData.params.id)
        return {code: 200, data: isDel, message: isDel ? '操作成功' : '操作失败'}
    }

    async getFunctionParamsList(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.PARAMETERS where SPECIFIC_SCHEMA='${localData.params.databaseKey}' AND SPECIFIC_NAME='${localData.params.specificName}' ORDER BY ORDINAL_POSITION;`);
    }

    async getQueryViews(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.VIEWS where TABLE_SCHEMA='${localData.params.databaseKey}';`);
    }

    async getFunctionList(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.ROUTINES where ROUTINE_SCHEMA='${localData.params.databaseKey}';`);
    }

    async getTableIndexList(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.STATISTICS where TABLE_NAME='${localData.params.tableKey}';`);
    }

    async getTableOutsideList(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA='${localData.params.databaseKey}' AND TABLE_NAME='${localData.params.tableKey}' AND CONSTRAINT_NAME!='PRIMARY' ;`);
    }

    async getTableTriggersList(connect){
        let localData = JSON.parse(connect);
        return await new MysqlDB().executeQuery(localData, `SELECT * FROM information_schema.TRIGGERS where EVENT_OBJECT_SCHEMA='${localData.params.databaseKey}' AND EVENT_OBJECT_TABLE='${localData.params.tableKey}' ORDER BY CREATED ;`);
    }
    


}

MysqlMngService.toString = () => '[class MysqlMngService]';
module.exports = MysqlMngService;
